package com.dudu.dao;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import com.dudu.bean.CompanyUserBean;

import core.CoreDao;
import core.CoreDaoInvoke;
import core.db.DBConnectionUtil;

public class CompanyUserDao {

	public static void main(String[] args) {

		CoreDao dao = new CoreDaoInvoke();
		CompanyUserDao dao1 = new CompanyUserDao();
		CompanyUserBean bean = new CompanyUserBean();

		bean.phone = "1";
		bean.is_use = "1";
		bean.user_identifer = "1";

		int result = dao1.insertUser(dao, bean);
	}

	// 返回的是主键ID
	public int insertUser(CoreDao dao, CompanyUserBean bean) {

		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"insert into t_company_user(name,phone,create_time,user_identifer, area_sheng, area_shi,app_id,account_id,company_id) ");
			sql.append(" values(?,?,sysdate(),?,?,?,?,?,?)");

			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { bean.getName(), bean.phone, bean.user_identifer, bean.getArea_sheng(),
							bean.getArea_shi(), bean.getApp_id(), bean.getAccount_id(), bean.getCompany_id()

			});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	// 返回的影响的行数据
	public int deleteUser(CoreDao dao, CompanyUserBean bean) {

		Connection con = null;
		int result = -1;

		try {

			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer("delete from t_company_user where phone =");
			sql.append("?");

			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { bean.phone });

			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}

		return result;

	}

	// 影响的记录数
	public int updateUserPhone(CoreDao dao, CompanyUserBean bean) {

		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer("update t_company_user set phone=?, area_sheng=?, area_shi=?");
			sql.append(" where id=?");

			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { bean.getPhone(), bean.getArea_sheng(), bean.getArea_shi(), bean.getId() });

			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}

		return result;
	}

	//
	public List<CompanyUserBean> queryUserbyPhone(CoreDao dao, String phone) {

		Connection con = null;
		List<CompanyUserBean> users = null;
		try {

			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					"select app_id,account_id,name,password,phone,is_use,email,qq,create_time,comments,user_identifer,area_sheng, area_shi from t_company_user where phone =");
			sql.append("?");

			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { phone });

			if (list != null && list.size() > 0) {

				users = covertToBean(list);
				// result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}

		return users;
	}

	public CompanyUserBean queryUserbyIdentifer(CoreDao dao, String user_identifier, int company_id) {

		Connection con = null;
		CompanyUserBean result = null;
		try {

			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"select u.id, u.app_id,u.account_id,u.name,u.phone,u.is_use,u.email,u.qq,u.create_time,u.comments,u.user_identifer,u.area_sheng, u.area_shi ,a.company_id from t_company_user u,t_account a where ");
			sql.append("u.account_id=a.id and u.user_identifer =? and a.company_id=?");
			sql.append(" order by create_time desc");

			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { user_identifier, company_id });

			if (list != null && list.size() > 0) {

				List<CompanyUserBean> items = covertToBean(list);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	public CompanyUserBean queryUserbyIdentiferAndCompanyId(CoreDao dao, String user_identifier, int app_id) {

		Connection con = null;
		CompanyUserBean result = null;
		try {

			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					"select id,app_id,account_id,name,phone,is_use,email,qq,create_time,comments,user_identifer,area_sheng, area_shi from t_company_user where user_identifer =");
			sql.append("?");
			sql.append(" and app_id=? ");
			sql.append(" order by create_time desc");

			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { user_identifier, app_id });

			if (list != null && list.size() > 0) {

				List<CompanyUserBean> items = covertToBean(list);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	public CompanyUserBean queryUserbyIdentiferAndAccountId(CoreDao dao, String user_identifier, int accountId,
			int app_id) {

		Connection con = null;
		CompanyUserBean result = null;
		try {

			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					"select id,app_id,account_id,name,phone,is_use,email,qq,create_time,comments,user_identifer,area_sheng, area_shi "
							+ " from t_company_user ");
			sql.append(" where user_identifer =? ");
			sql.append(" and account_id=? ");
			sql.append(" and app_id=? ");
			sql.append(" order by create_time desc");

			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { user_identifier, accountId, app_id });

			if (list != null && list.size() > 0) {

				List<CompanyUserBean> items = covertToBean(list);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	public CompanyUserBean queryUserbyIdentiferAndPhone(CoreDao dao, String phone, String identifier) {

		Connection con = null;
		CompanyUserBean result = null;
		try {

			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					"select id,app_id,account_id,name,password,phone,is_use,email,qq,create_time,comments,user_identifer,area_sheng, area_shi from t_company_user where phone =");
			sql.append("?");
			sql.append(" and user_identifer=");
			sql.append("?");

			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { phone, identifier });

			if (list != null && list.size() > 0) {

				List<CompanyUserBean> items = covertToBean(list);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	public CompanyUserBean queryUserbyAppAndCompnayIdentifer(CoreDao dao, String user_identifier, String company_idendify,
			String app_identify) {

		Connection con = null;
		CompanyUserBean result = null;
		try {

			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer(
					" select u.* from t_company_user u, t_company c WHERE u.company_id = c.id ");
			sql.append(" AND c.app_identify = ? AND c.identify = ? AND u.user_identifer = ? ");

			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { app_identify, company_idendify, user_identifier });

			if (list != null && list.size() > 0) {

				List<CompanyUserBean> items = covertToBean(list);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	private List<CompanyUserBean> covertToBean(List<JSONObject> objs) {

		List<CompanyUserBean> list = new ArrayList<CompanyUserBean>();
		for (int i = 0; i < objs.size(); i++) {
			JSONObject json = objs.get(i);
			CompanyUserBean bean = (CompanyUserBean) JSONObject.toBean(json, CompanyUserBean.class);
			list.add(bean);
		}
		return list;
	}

	public Map<String, Object> queryUserInfoDetails(CoreDao dao, int appId, int companyId) {

		Connection con = null;
		Map<String, Object> result = null;
		try {

			con = DBConnectionUtil.getDBConnection("emate1");

			StringBuffer sql = new StringBuffer("select	cp.identify,app.name from t_company cp,t_app app where ");
			sql.append("app.id = ? AND cp.app_id = app.id AND cp.id = ? ");

			List<Map<String, Object>> list = dao.servcieSql(con, sql.toString(), new Object[] { appId, companyId });

			if (list != null && list.size() > 0) {
				result = list.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

}
